package com.example.othree.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.example.othree.entity.Lidar;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

@Component
public interface LidarMapper extends BaseMapper<Lidar> {

    //日平均的数据
    @Select("SELECT DATE(datetime) AS datetime,  `${yaosu}` as extino3  " +
//            "SELECT DATE(datetime) AS datetime, extino3, co3, chaprr, chasnr " +
            "FROM lidar_ozone_observation_data " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime} " +
            "And station_name = #{station}" +
            "ORDER BY DATE(datetime)")
    List<Lidar> selectDayAverage(String startTime, String endTime, String station, String yaosu);

    //日平均的平均层高
    @Select("SELECT  " +
            "DATE(datetime) as datetime, AVG(pbl) as pbl " +
            "FROM   lidar_ozone_observation_data " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime}" +
            "And station_name = #{station} " +
            "GROUP BY DATE(datetime)")
    ArrayList<Lidar> selectDayAveragePbl(String startTime, String endTime, String station);

    //月平均的数据
    @Select("SELECT  " +
//            "DATE_FORMAT(datetime, '%Y-%m') as datetime, extino3, co3, chaprr, chasnr " +
            "DATE_FORMAT(datetime, '%Y-%m') as datetime, `${yaosu}` as extino3 " +
            "FROM   lidar_ozone_observation_data " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime}" +
            "And station_name = #{station} " +
            "ORDER BY CONCAT(YEAR(datetime), '-', LPAD(MONTH(datetime), 2, '0')) ")
    List<Lidar> selectMonthAverage(String startTime, String endTime, String station, String yaosu);

    //月平均的平均层高
    @Select("SELECT  " +
            "DATE_FORMAT(datetime, '%Y-%m') as datetime, AVG(pbl) as pbl " +
            "FROM   lidar_ozone_observation_data " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime}" +
            "And station_name = #{station} " +
            "GROUP BY CONCAT(YEAR(datetime), '-', LPAD(MONTH(datetime), 2, '0')) ")
    ArrayList<Lidar> selectMonthAveragePbl(String startTime, String endTime, String station);

    //时平均的数据
    @Select("SELECT " +
            "DATE_FORMAT(datetime, '%Y-%m-%d %H') as datetime,  `${yaosu}` as extino3 " +
//            "DATE_FORMAT(datetime, '%Y-%m-%d %H') as datetime, extino3, co3, chaprr, chasnr " +
            "FROM lidar_ozone_observation_data  " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime}" +
            "And station_name = #{station} " +
            "ORDER BY CONCAT(DATE(datetime), ':', LPAD(HOUR(datetime), 2, '0')) ")
    List<Lidar> selectHourAverage(String startTime, String endTime, String station, String yaosu);

    //时平均的平均层高
    @Select("SELECT " +
            "DATE_FORMAT(datetime, '%Y-%m-%d %H') as datetime, AVG(pbl) as pbl " +
            "FROM lidar_ozone_observation_data  " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime}" +
            "And station_name = #{station} " +
            "GROUP BY CONCAT(DATE(datetime), ':', LPAD(HOUR(datetime), 2, '0')) ")
    ArrayList<Lidar> selectHourAveragePbl(String startTime, String endTime, String station);

    //10分平均的数据
    @Select("SELECT " +

            "DATE_FORMAT(datetime, '%Y-%m-%d %H:%i') as datetime, `${yaosu}` as extino3 " +
//            "DATE_FORMAT(datetime, '%Y-%m-%d %H:%i') as datetime, extino3, co3, chaprr, chasnr " +
            "FROM lidar_ozone_observation_data  " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime}" +
            "And station_name = #{station} " +
            "ORDER BY datetime ")
    List<Lidar> selectMinAverage(String startTime, String endTime, String station, String yaosu);

    //10分平均的平均层高
    @Select("SELECT " +
            "DATE_FORMAT(datetime, '%Y-%m-%d %H:%i'), AVG(pbl) as pbl " +
            "FROM lidar_ozone_observation_data  " +
            "WHERE datetime BETWEEN #{startTime} AND #{endTime}" +
            "And station_name = #{station} " +
            "GROUP BY datetime ")
    ArrayList<Lidar> selectMinAveragePbl(String startTime, String endTime, String station);

}
